﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
using System.Web;
using DotNet.Common;
using System.Data.SqlClient;
using System.Collections;
namespace DotNet.Data.Factory
{
    /// <summary>
    ///Access数据库访问类
    /// </summary>
    public class AccHelp : IDBHelp,IDisposable
    {
        #region 定义全局 数据源
        private static readonly string connectionString = "provider=Microsoft.jet.OLEDB.4.0;data Source=" + CommUtil.GetAppPath(1) + ConfigurationManager.ConnectionStrings["Zrnet_AccConnString"].ToString();
        private OleDbCommand oleCmd =null;
        private OleDbConnection oleCon = null;
        private OleDbDataAdapter oleDa = null;
        bool bShowSql = IsShowSql();      
        private static bool IsShowSql()
        {
            bool b = false;
            if (ConfigurationManager.AppSettings["Zrnet_ShowSql"] != "")
            {
                b = Convert.ToBoolean(ConfigurationManager.AppSettings["Zrnet_ShowSql"]);
            }
            return b;
        }
        #endregion

        public DataTable GetDataTable(string commandText)
        {
            DataTable dt = new DataTable();
            try
            {
                oleCon = new OleDbConnection(connectionString);
                OpenConnection();
                oleCmd = CreateCommd(oleCon, null, commandText, null);
                oleDa = new OleDbDataAdapter(oleCmd);
                oleDa.Fill(dt);
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                oleDa.Dispose();
                oleCmd.Dispose();
                CloseConnection();
            }
            return dt;
        }

        public DataTable GetDataTable(string commandText, Hashtable ht)
        {
            DataTable dt = new DataTable();
            try
            {
                oleCon = new OleDbConnection(connectionString);
                OpenConnection();
                oleCmd = CreateCommd(oleCon, null, commandText, ht);
                oleDa = new OleDbDataAdapter(oleCmd);
                oleDa.Fill(dt);      
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                oleDa.Dispose();
                oleCmd.Dispose();
                dt.Dispose();
                CloseConnection();
            }
            return dt;
        }

        public DataSet GetDataSet(string commandText)
        {
            DataSet ds = new DataSet();
            try
            {               
                ds.Tables.Add(GetDataTable(commandText));               
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                ds.Dispose();
            }
            return ds;
        }
        public DataSet GetDataSet(string commandText, Hashtable ht)
        { 
            DataSet ds = new DataSet();
            try
            {               
                ds.Tables.Add(GetDataTable(commandText, ht));               
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);

            }
            finally
            {
                ds.Dispose();
            }
            return ds;
        }
        public string GetConnString()
        {
            return "provider=Microsoft.jet.OLEDB.4.0;data Source=" + CommUtil.GetAppPath(1) + ConfigurationManager.ConnectionStrings["Zrnet_AccConnString"].ToString();
        }

        public Object ExecuteScalar(string commandText)
        {
            object ob = null;
            try
            {
               oleCon=new OleDbConnection(connectionString);
                OpenConnection();
                oleCmd=CreateCommd(oleCon,null,commandText,null);
                ob=oleCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                oleCmd.Dispose();
                CloseConnection();
            }
            return ob;

        }
        public Object ExecuteScalar(string commandText, Hashtable ht)
        {
            object ob = null;
            try
            {
                oleCon = new OleDbConnection(connectionString);
                OpenConnection();
                oleCmd = CreateCommd(oleCon, null, commandText, ht);
                ob = oleCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                oleCmd.Dispose();
                CloseConnection();
            }
            return ob;
        }
        public int ExecuteNonQuery(string commandText)
        {
            int i = 0;
            try
            {
                oleCon = new OleDbConnection(connectionString);
                OpenConnection();
                oleCmd = CreateCommd(oleCon, null, commandText, null);
                i = oleCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);

            }           
            finally
            {
                oleCmd.Dispose();
                CloseConnection();
            }
            return i;
        }
        public int ExecuteNonQuery(string commandText, Hashtable ht)
        {
            oleCon = new OleDbConnection(connectionString);
            OpenConnection();
            OleDbTransaction oleTrans = oleCon.BeginTransaction();
            int val = 0;
            try
            {
                oleCmd = CreateCommd(oleCon, oleTrans, commandText, ht);
                val = oleCmd.ExecuteNonQuery();
                oleTrans.Commit();
            }
            catch (Exception ex)
            {
                oleTrans.Rollback();
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally 
            {
                oleCmd.Dispose();
                oleTrans.Dispose();
                CloseConnection();
            }
            return val;

        }
        public bool IsExists(string commandText)
        {
            bool ret = false;
            try
            {
                object obj = ExecuteScalar(commandText);
                if (obj != null && obj.ToString() != "0")
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                
            }
            return ret;
        }
        public bool IsExists(string commandText, Hashtable ht)
        {
            bool ret = false;
            try
            {
                object obj = ExecuteScalar(commandText, ht);
                if (obj != null && obj.ToString() != "0")
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                
            }
            return ret;
        }
        public int GetRecordCount(string commandText)
        {
            object o = ExecuteScalar(commandText);
            if (o != null)
            {
                return int.Parse(o.ToString());
            }
            else
            {
                return 0;
            }
        }
        public int GetRecordCount(string commandText, Hashtable ht)
        {
            object o = ExecuteScalar(commandText, ht);
            if (o != null)
            {
                return int.Parse(o.ToString());
            }
            else
            {
                return 0;
            }
        }
        public IList<string> GetList(string commandText)
        {
            DataTable dt = GetDataTable(commandText);
            List<string> il = new List<string>();
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows[0].ItemArray.Length; i++)
                        il.Add(dt.Rows[0].ItemArray.GetValue(i).ToString());
                }
                return il;
            }
            else
            {
                return null;
            }
        }
        public IList<string> GetList(string commandText, Hashtable ht)
        {
            DataTable dt = GetDataTable(commandText, ht);
            List<string> il = new List<string>();
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows[0].ItemArray.Length; i++)
                        il.Add(dt.Rows[0].ItemArray.GetValue(i).ToString());
                }
                return il;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取分页数据表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="keyField"></param>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="orderField"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public DataTable GetPageDataTable(int pageIndex, int pageSize, string fields, string tableName, string conditions, string orderField, bool isDesc)
        {
            string sql = "";
            try
            {
                int recordCount = 0;
                object objRecordCount = ExecuteScalar("select count(1) from " + tableName + " where 1 > 0 " + conditions + "");
                if (objRecordCount != null)
                {
                    recordCount = int.Parse(objRecordCount.ToString());
                }
                int lastPageSize = recordCount % pageSize == 0 ? pageSize : (recordCount % pageSize);
                string o1, o2;
                if (isDesc)
                {
                    o1 = "desc";
                    o2 = "asc";
                }
                else
                {
                    o1 = "asc";
                    o2 = "desc";
                }
                int pageCount = recordCount % pageSize == 0 ? (recordCount / pageSize) : (CommUtil.GetInt(recordCount / pageSize) + 1);
                if (pageIndex <= 1)
                {
                    sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o1;
                }
                else
                {
                    if (pageIndex >= pageCount)
                    {
                        sql = "select * from (select top " + lastPageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o2 + ")table1 order by " + orderField + " " + o1;
                    }
                    else
                    {
                        sql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o1 + ")table1 order by " + orderField + " " + o2 + ")table2 order by " + orderField + " " + o1;
                    }
                }
                DataTable dt = GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = sql;
                throw new Exception(tsql + " " + ex.Message);
            }
        }

        /// <summary>
        ///  大数据量快速分页,50万以上数据分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="orderField"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public DataTable GetMPageDataTable(int pageIndex, int pageSize, string fields, string tableName, string conditions, string orderField, bool isDesc)
        {
            string sql = "";
            try
            {
                if (isDesc)
                {
                    if (pageIndex <= 1)
                    {
                        sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " desc";
                    }
                    else
                        sql = "Select Top " + pageSize + " " + fields + " From " + tableName + " Where " + orderField + "<(Select Min(" + orderField + ") From (Select Top " + (pageIndex - 1) * pageSize + " " + orderField + " From " + tableName + " where 1=1 " + conditions + " Order By " + orderField + " desc) T) Order By " + orderField + " desc ";

                }
                else
                {
                    if (pageIndex <= 1)
                    {
                        sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " asc";
                    }
                    else
                        sql = "Select Top " + pageSize + " " + fields + " From " + tableName + " Where " + orderField + ">(Select Max(" + orderField + ") From (Select Top " + (pageIndex - 1) * pageSize + " " + orderField + " From " + tableName + " where 1=1 " + conditions + " Order By " + orderField + " Asc) T) Order By " + orderField + " Asc";

                }

                DataTable dt = GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = sql;
                throw new Exception(tsql + " " + ex.Message);
            }
        }



        #region 装载命令
        /// <summary>
        /// 整合ACC和MSSQL数据库参数差异化配置
        /// </summary>
        /// <param name="sqlConn">数据库连接对象</param>
        /// <param name="transaction">事务对象</param>
        /// <param name="sqlStr">执行的SQL语句</param>
        /// <param name="ht">HASH表</param>
        /// <returns></returns>
        public OleDbCommand CreateCommd(OleDbConnection oleCon, OleDbTransaction oleTransaction, string oleStr, Hashtable ht)
        {
            OleDbCommand oleCmd = new OleDbCommand();
            oleCmd.Connection = oleCon;
            oleCmd.CommandText = oleStr;
            oleCmd.CommandType = CommandType.Text;
            if (oleTransaction != null)
            {
                oleCmd.Transaction = oleTransaction;
            }
            if (ht != null)
            {
                OleDbParameter[] oleParam = new OleDbParameter[ht.Count];
                int i = 0;
                foreach (DictionaryEntry objDE in ht)
                {
                    oleParam[i] = new OleDbParameter(objDE.Key.ToString(), null);
                    if (objDE.Value == null)
                    {
                        oleParam[i++].Value = DBNull.Value;
                    }
                    else
                        oleParam[i++].Value = objDE.Value;
                }
                oleCmd.Parameters.AddRange(oleParam);
            }

            return oleCmd;
        }
        public void OpenConnection()
        {
            if (oleCon.State == ConnectionState.Broken || oleCon.State == ConnectionState.Closed)
                oleCon.Open();
        }
        public void CloseConnection()
        {
            if (oleCon != null)
            oleCon.Close();
        }
        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
